package com.base.helper;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

/**
 * @author mzl
 * @desc
 * @date 2020-06-01
 * @since 1.0.0
 */
public class ExcelCommon {

    private static final Logger LOG = LoggerFactory.getLogger(ExcelCommon.class);

    /**
     * 校验表格格式
     * @param tempFile
     * @param fis
     * @return
     */
    public static Workbook checkExcel(File tempFile, FileInputStream fis) {
        try {
            fis = new FileInputStream(tempFile);
            return new HSSFWorkbook(fis);
        } catch (Exception e) {
            try {
                //这里不创建输入流就会报错stream close
                fis = new FileInputStream(tempFile);
                return new XSSFWorkbook(fis);
            } catch (Exception e1) {
                //返回文件格式错误异常
                throw new RuntimeException("导入excel格式错误");
            }//得到工作簿
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    LOG.error(e.getMessage());
                    throw new RuntimeException("导入excel格式错误");
                }
            }
        }
    }

    /**
     * 读取单元格内容
     * @param cellType
     * @param row
     * @param i
     * @return
     */
    public static String readFromCell(int cellType, Row row, int i) {
        Cell cell = row.getCell(i);
        if (cell == null) {
            return "";
        } else {
            cell.setCellType(cellType);
            return cell.getStringCellValue().trim();
        }
    }

    /**
     * 转换文件格式
     * @param file
     * @param request
     * @return
     */
    public static File transferTo(MultipartFile file, HttpServletRequest request) {
        //获取用户上传表格的地址，执行完程序后表格将会删除，避免占用内存
        String filePath = request.getSession().getServletContext().getRealPath("/");
        //根据地址和文件名准确获取用户上传的表格
        File tempFile = new File(filePath + file.getOriginalFilename());
        // ExcelReader
        //将MultipartFile转换为File类型
        try {
            file.transferTo(tempFile);
            return tempFile;
        } catch (IOException e) {
            tempFile.delete();
            throw new RuntimeException("导入excel格式错误");
        }
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static CellRangeAddress isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range       = sheet.getMergedRegion(i);
            int              firstColumn = range.getFirstColumn();
            int              lastColumn  = range.getLastColumn();
            int              firstRow    = range.getFirstRow();
            int              lastRow     = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return range;
                }
            }
        }
        return null;
    }

}
